The data files are from two mobility companies. We are interested in learning more about the dynamics of the market in which they operate.
- Describe the operations of these two mobility companies.
- Please estimate the number of scooters company b operates in this geography? Simpler mechanisms of estimation are preferred
Important Python Libraries¶
In [1]:
import warnings
warnings.filterwarnings("ignore")
import glob
import pandas as pd
import matplotlib.pyplot as plt
plt.style.use('fivethirtyeight')
import seaborn as sns
In [2]:
files = glob.glob("*.csv")
print("There are {} data files (training data as well as testing data)".format(len(files)))
files
Out[2]:
Data Loading¶
In [3]:
company_a_1 = pd.read_csv("company_a_1.csv")
company_a_1.head()
Out[3]:
In [4]:
company_a_2 = pd.read_csv("company_a_2.csv")
company_a_2.head()
Out[4]:
In [5]:
company_a_3 = pd.read_csv("company_a_3.csv")
company_a_3.head()
Out[5]:
In [6]:
company_a_4 = pd.read_csv("company_a_4.csv")
company_a_4.head()
Out[6]:
In [7]:
company_a_5 = pd.read_csv("company_a_5.csv")
company_a_5.head()
Out[7]:
In [8]:
company_b_1 = pd.read_csv("company_b_1.csv")
company_b_1.head()
Out[8]:
In [9]:
company_b_2 = pd.read_csv("company_b_2.csv")
company_b_2.head()
Out[9]:
Distances in test data is in meters, while training it is Kilometers, so convert distance to Kilometer first.
In [10]:
import geopy.distance
coords_1 = (38.23, -85.75)
coords_2 = (38.25, -85.76)
print(geopy.distance.geodesic(coords_1, coords_2).km)
Combine Company A Data into One DataFrame¶
In [11]:
training_data = pd.concat([company_a_1, company_a_2, company_a_3, company_a_4, company_a_5])
training_data.shape
Out[11]:
Combine Company B Data into One DataFrame¶
In [12]:
testing_data = pd.concat([company_b_1, company_b_2])
testing_data.shape
Out[12]:
Convert start_time and completed_time to Date Type¶
In [13]:
training_data["StartTime"] = pd.to_datetime(training_data["StartTime"])
training_data["EndTime"] = pd.to_datetime(training_data["EndTime"])
testing_data["start_time"] = pd.to_datetime(testing_data["start_time"])
testing_data["completed_time"] = pd.to_datetime(testing_data["completed_time"])
Extract Year, Month, Day, Hour from Start Date columns¶
Also calculate trip duration using formula
$$tripDuration = time_{start} - time_{end}$$In [14]:
training_data["year"] = training_data["StartTime"].dt.year
testing_data["year"] = testing_data["start_time"].dt.year
training_data["month"] = training_data["StartTime"].dt.month_name()
testing_data["month"] = testing_data["start_time"].dt.month_name()
training_data["day"] = training_data["StartTime"].dt.day_name()
testing_data["day"] = testing_data["start_time"].dt.day_name()
training_data["hour"] = training_data["StartTime"].dt.hour
testing_data["hour"] = testing_data["start_time"].dt.hour
training_data["tripDuration(minutes)"] = round((training_data["EndTime"] - training_data["StartTime"]).dt.total_seconds()/60)
testing_data["tripDuration(minutes)"] = round((testing_data["completed_time"] - testing_data["start_time"]).dt.total_seconds()/60)
training_data.head()
Out[14]:
Unique Values¶
In [15]:
training_data.nunique()
Out[15]:
In [16]:
testing_data.nunique()
Out[16]:
Missing Values Analysis¶
In [17]:
training_data.isna().sum()
Out[17]:
In [18]:
testing_data.isna().sum()
Out[18]:
In [19]:
training_data.columns
Out[19]:
Check for Anamolies/ Outliers¶
- We are looking for issues in datasets
- There might be invalid values such as
- Trip distance must be greater than ZERO
- Trip Duration must be greater than ZERO
- Very large values for duration and distance
- There might be invalid locations such as latitude/ longitude of other state or country
In [20]:
import matplotlib.pyplot as plt
from matplotlib.pyplot import figure
import numpy as np
def plotOutliers(data, c):
print("Feature Name:", c)
figure(figsize=(18, 8), dpi=80)
plt.plot([i for i in range(len(data[c].values))], data[c].values)
df = np.array(data[c])
print("Highest allowed",np.mean(df) + 3* np.std(df))
print("Lowest allowed",np.mean(df) - 3* np.std(df))
H = np.mean(df) + 3* np.std(df)
L = np.mean(df) - 3* np.std(df)
values = df[(df > H) | (df < L)]
if len(values) < 10:
print(values)
R = []
for i in data[c].values:
if i > H or i < L:
R.append(i)
else:
R.append(data[c].mean())
plt.plot([i for i in range(len(data[c].values))], R, linestyle='None', marker="*", markersize=15, label="Outliers")
plt.title(c)
plt.legend()
plt.show()
In [21]:
c = 'StartLatitude'
plotOutliers(training_data, c)
In [22]:
c = 'StartLongitude'
plotOutliers(training_data, c)
In [23]:
c = 'EndLatitude'
plotOutliers(training_data, c)
There are some invalid locations i.e locations outside city.
In [24]:
invalid_locations = [-122.42, -111.87, -111.87, -111.87, 26.12]
training_data = training_data[~training_data["EndLatitude"].isin(invalid_locations)]
training_data = training_data[training_data["EndLatitude"] != 0]
In [25]:
c = 'EndLongitude'
plotOutliers(training_data, c)
In [26]:
training_data = training_data[~training_data["EndLongitude"].isna()]
training_data = training_data[~training_data["EndLongitude"].isin(invalid_locations)]
training_data = training_data[training_data["EndLongitude"] != 0]
In [27]:
c = 'TripDistance'
plotOutliers(training_data, c)
In [28]:
invalid_values = [1111734.31, 1334335.28, 1334377.66, 1213527.47, 1334328.7, 822605.85, 1327569.19]
training_data = training_data[~training_data["TripDistance"].isin(invalid_values)]
training_data = training_data[training_data["TripDistance"] > 0]
In [29]:
c = 'tripDuration(minutes)'
plotOutliers(training_data, c)
In [30]:
invalid_values = [3167, 2843]
training_data = training_data[~training_data["tripDuration(minutes)"].isin(invalid_values)]
training_data = training_data[training_data["tripDuration(minutes)"] > 0]
In [31]:
c = 'TripDistance'
plotOutliers(training_data, c)
In [32]:
training_data = training_data[(training_data["TripDistance"] < 2000) & (training_data["TripDistance"] > 0)]
Pre-process Company B Data as well¶
In [33]:
c = 'start_latitude'
plotOutliers(testing_data, c)
In [34]:
c = 'start_longitude'
plotOutliers(testing_data, c)
In [35]:
c = 'end_latitude'
plotOutliers(testing_data, c)
The regions shown in red circles are the outliers, because usually this is not possible to have cross-state rides or very far rides.
In [36]:
invalid_values = [26.12, 19.42]
testing_data = testing_data[~testing_data["end_latitude"].isin(invalid_values)]
testing_data = testing_data[testing_data["end_latitude"] != 0]
In [37]:
c = 'end_longitude'
plotOutliers(testing_data, c)
In [38]:
invalid_values = [-111.94, 2.36, 2.32, 2.33, -121.89, -119.81]
testing_data = testing_data[~testing_data["end_longitude"].isin(invalid_values)]
testing_data = testing_data[testing_data["end_longitude"] != 0]
In [39]:
c = 'tripDuration(minutes)'
plotOutliers(testing_data, c)
In [40]:
testing_data = testing_data[(testing_data["tripDuration(minutes)"] < 400) & (testing_data["tripDuration(minutes)"] > 0)]
In [41]:
c = 'distance_meters'
plotOutliers(testing_data, c)
In [42]:
testing_data = testing_data[(testing_data["distance_meters"] < 50000) & (testing_data["distance_meters"] > 0)]
In [43]:
training_data.to_csv("company_A.csv", index=False)
testing_data.to_csv("company_B.csv", index=False)
Analzying Source and Destination Points (Company A)¶
Note: Figure 1 Generated using Power BI - Left (Starting Points of Journey) - Right (Ending Points of Journey)
Analzying Source & Destination Points (Company B)¶
Note: Figure 2 Generated using Power BI - Left (Starting Points of Journey) - Right (Ending Points of Journey)
Company seems to have same region i-e their scooters are also in same region as company A. The only difference is company have complete city whereas company B has access in specific region (but not limited to that region).¶
Facts About This Location¶
Note: This external data can be used for deep and detailed analysis.
city = Louisville¶
county = Jefferson County¶
state = Kentucky¶
postcode = 40202¶
country = United States¶
https://censusreporter.org/profiles/05000US21111-jefferson-county-ky/
The population of mentioned state is good enough to target for company.
- There are 2000 people per square mile which means that
- In best case, if all use scooter, then you need 766000 scooters
- If 50% of the population use scooters, then you need half of the above.
- There are 61% (473000) of the population which are age between
18to64years. They have high chances to use scooter. Rest are either children or elders which seldom use scooters. Hence the use of scooter reduced from766000to473000(if all people use scooter) - The native people are like 67% percent of the population. They have more chances to have own ride and avoid scooter. If we can 50% of this, the need of scooter reduced from
473000to238000(which is50%of473000)
By checking the poverty index and the people who use public transport + walked + other, the need of scooters further reduced.¶
Compare both Datasets using Visual Analysis¶
In [44]:
def showHist(columnName1, columnName2):
fig, a = plt.subplots(1, 2, figsize=(20, 5))
try:
a[0].hist(training_data[columnName1])
a[1].hist(testing_data[columnName2])
except:
training_data[columnName1].value_counts().head(20).plot(kind='barh', ax=a[0]).sort_index()
testing_data[columnName2].value_counts().head(20).plot(kind='barh', ax=a[1]).sort_index()
a[0].set_title(columnName1 + ' - Company A')
a[1].set_title(columnName2 + ' - Company B')
fig.tight_layout()
plt.subplots_adjust(left=0.125,
bottom=0.1,
right=0.9,
top=0.9,
wspace=0.4,
hspace=0.35)
plt.show()
In [45]:
showHist("StartLatitude", "start_latitude")
In [46]:
showHist("StartLongitude", "start_longitude")
In [47]:
showHist("EndLatitude", "end_latitude")
In [48]:
showHist("EndLongitude", "end_longitude")
In [49]:
training_data["distance_meters"] = training_data["TripDistance"] * 1000
testing_data["distance_meters"].min()
Out[49]:
In [50]:
showHist("distance_meters", "distance_meters")
In [51]:
showHist("year", "year")
In [52]:
showHist("day", "day")
In [53]:
showHist("hour", "hour")
In [54]:
showHist("month", "month")
In [55]:
showHist("tripDuration(minutes)", "tripDuration(minutes)")
Observations¶
By looking at above histograms, I have concluded following
- The Company A data is of year 2018 and company B is of 2018 and 2019
- Data for comparison should be of same period (Take only 2018 Data)
- The data of company A is of months (August, September, October, Nov, and December) while the data of company B is of months (Jan and December)
- We need to take common data for comparison i-e take data of month December
In [56]:
print("Look at the Year Wise Data - Company A")
print(training_data["year"].value_counts())
print("\n\nLook at the Year Wise Data - Company B")
print(testing_data["year"].value_counts())
In [57]:
print('For analysis we need to get data of same year as Company A')
testing_data = testing_data[testing_data["year"] == 2018]
In [58]:
print("Take December Data from both Datasets")
training_data = training_data[training_data["month"] == "December"]
testing_data = testing_data[testing_data["month"] == "December"]
In [59]:
print('There are {} records in Company A'.format(training_data.shape[0]))
print('There are {} records in Company B'.format(testing_data.shape[0]))
In [60]:
training_data.nunique()
Out[60]:
In [61]:
scoters_trips = training_data[["ScooterID", "TripID"]].groupby(["ScooterID"]).count().reset_index()
scoters_trips = scoters_trips.sort_values(by="TripID", ascending=False)
scoters_trips
Out[61]:
In [62]:
print("There are total {} scooters of company A".format(scoters_trips["ScooterID"].nunique()))
average_trips_per_scooter = round(training_data["TripID"].nunique() / scoters_trips["ScooterID"].nunique())
print("On Average {} trips are done by single scooter".format(average_trips_per_scooter))
In [63]:
month_trips_a = training_data[["month", "TripID", "ScooterID"]].groupby(["month"]).nunique().reset_index()
month_trips_a = month_trips_a.sort_values(by="TripID", ascending=False)
print("Monthly Trips")
print("On Average company A have {} trips".format(round(month_trips_a["TripID"].mean())))
print("On Average company A have {} active scooters".format(round(month_trips_a["ScooterID"].mean())))
In [64]:
day_trips_a = training_data[["month","day","TripID", "ScooterID"]].groupby(["month","day"]).nunique().reset_index()
day_trips_a = day_trips_a.sort_values(by="TripID", ascending=False)
print("Daily Trips")
print("On Average company A have {} trips".format(round(day_trips_a["TripID"].mean())))
print("On Average company A have {} active scooters".format(round(day_trips_a["ScooterID"].mean())))
In [65]:
hourly_trips_a = training_data[["month","day", "hour","TripID", "ScooterID"]].groupby(["month","day","hour"]).nunique().reset_index()
hourly_trips_a = hourly_trips_a.sort_values(by="TripID", ascending=False)
print("Hourly Trips")
print("On Average company A have {} trips".format(round(hourly_trips_a["TripID"].mean())))
print("On Average company A have {} active scooters".format(round(hourly_trips_a["ScooterID"].mean())))
In [66]:
month_trips_b = testing_data[["month", "trip_id"]].groupby(["month"]).nunique().reset_index()
month_trips_b = month_trips_b.sort_values(by="trip_id", ascending=False)
print("Monthly Trips")
print("On Average company B have {} trips".format(round(month_trips_b["trip_id"].mean())))
In [67]:
day_trips_b = testing_data[["month","day", "trip_id"]].groupby(["month","day"]).nunique().reset_index()
print("Daily Trips")
print("On Average company B have {} trips".format(round(day_trips_b["trip_id"].mean())))
In [68]:
hourly_trips_b = testing_data[["month","day", "hour","trip_id"]].groupby(["month","day","hour"]).nunique().reset_index()
hourly_trips_b = hourly_trips_b.sort_values(by="trip_id", ascending=False)
print("Hourly Trips")
print("On Average company B have {} trips".format(round(hourly_trips_b["trip_id"].mean())))
Comparative Analysis¶
I am calculating different features for Company A and same features for Company B. Then finding ration of feature of company B to A using formula;
$$ratio_{feature_i} = \frac {Feature_{companyB}} {Feature_{companyA}}$$Then for calculating average of all ratios using
$$values = \frac {ratio_i} N$$Where N is the number of ratios.
The final comparative constant is calculated using weighted average; i.e
$$constant = \sum_{i=1}^N {values_i * weight_i}$$I have kept following weights;
0.3for all averages_values (avg_of_avg)0.2for all maximum_values (avg_of_maximum)0.2for all minimum_values (avg_of_minimum)0.3for all sum values (avg_of_sum)
Note: These weight values are calculated on the basis of optimization i-e hit and trial method used to calculate optimal value
In [69]:
print("Company A - Calculations")
print("-------------------------")
a_trips = training_data["TripID"].nunique()
a_trip_distance_minimum = training_data["distance_meters"].min()
a_trip_distance_maximum = training_data["distance_meters"].max()
a_trip_distance_average = training_data["distance_meters"].mean()
a_trip_distance_sum = training_data["distance_meters"].sum()
a_trip_duration_minimum = training_data["tripDuration(minutes)"].min()
a_trip_duration_maximum = training_data["tripDuration(minutes)"].max()
a_trip_duration_average = training_data["tripDuration(minutes)"].mean()
a_trip_duration_sum = training_data["tripDuration(minutes)"].sum()
a_average_monthly_trip = round(month_trips_a["TripID"].mean())
a_minimum_monthly_trip = round(month_trips_a["TripID"].min())
a_maximum_monthly_trip = round(month_trips_a["TripID"].max())
a_average_daily_trip = round(day_trips_a["TripID"].mean())
a_minimum_daily_trip = round(day_trips_a["TripID"].min())
a_maximum_daily_trip = round(day_trips_a["TripID"].max())
a_average_hourly_trip = round(hourly_trips_a["TripID"].mean())
a_minimum_hourly_trip = round(hourly_trips_a["TripID"].min())
a_maximum_hourly_trip = round(hourly_trips_a["TripID"].max())
In [70]:
print("Company B - Calculations")
print("-------------------------")
b_trips = testing_data["trip_id"].nunique()
b_trip_distance_minimum = testing_data["distance_meters"].min()
b_trip_distance_maximum = testing_data["distance_meters"].max()
b_trip_distance_average = testing_data["distance_meters"].mean()
b_trip_distance_sum = testing_data["distance_meters"].sum()
b_trip_duration_minimum = testing_data["tripDuration(minutes)"].min()
b_trip_duration_maximum = testing_data["tripDuration(minutes)"].max()
b_trip_duration_average = testing_data["tripDuration(minutes)"].mean()
b_trip_duration_sum = testing_data["tripDuration(minutes)"].sum()
b_average_monthly_trip = round(month_trips_b["trip_id"].mean())
b_minimum_monthly_trip = round(month_trips_b["trip_id"].min())
b_maximum_monthly_trip = round(month_trips_b["trip_id"].max())
b_average_daily_trip = round(day_trips_b["trip_id"].mean())
b_minimum_daily_trip = round(day_trips_b["trip_id"].min())
b_maximum_daily_trip = round(day_trips_b["trip_id"].max())
b_average_hourly_trip = round(hourly_trips_b["trip_id"].mean())
b_minimum_hourly_trip = round(hourly_trips_b["trip_id"].min())
b_maximum_hourly_trip = round(hourly_trips_b["trip_id"].max())
In [71]:
b_a_trip_ratio = b_trips / a_trips
b_a_distance_min_ratio = b_trip_distance_minimum / a_trip_distance_minimum
b_a_distance_max_ratio = b_trip_distance_maximum / a_trip_distance_maximum
b_a_distance_avg_ratio = b_trip_distance_average / a_trip_distance_average
b_a_distance_sum_ratio = b_trip_distance_sum / a_trip_distance_sum
b_a_duration_min_ratio = b_trip_duration_minimum / a_trip_duration_minimum
b_a_duration_max_ratio = b_trip_duration_maximum / a_trip_duration_maximum
b_a_duration_avg_ratio = b_trip_duration_average / a_trip_duration_average
b_a_duration_sum_ratio = b_trip_duration_sum / a_trip_duration_sum
b_a_average_monthly_trip = b_average_monthly_trip / a_average_monthly_trip
b_a_minimum_monthly_trip = b_minimum_monthly_trip / a_minimum_monthly_trip
b_a_maximum_monthly_trip = b_maximum_monthly_trip / a_maximum_monthly_trip
b_a_average_daily_trip = b_average_daily_trip / a_average_daily_trip
b_a_minimum_daily_trip = b_minimum_daily_trip / a_minimum_daily_trip
b_a_maximum_daily_trip = b_maximum_daily_trip / a_maximum_daily_trip
b_a_average_hourly_trip = b_average_hourly_trip / a_average_hourly_trip
b_a_minimum_hourly_trip = b_minimum_hourly_trip / a_minimum_hourly_trip
b_a_maximum_hourly_trip = b_maximum_hourly_trip / a_maximum_hourly_trip
print("Calculate Ratios")
print("-----------------------------------------------")
print("Number of Trips = ", b_a_trip_ratio)
print("-----------------------------------------------")
print("Minimum Distance Ratio = ", b_a_distance_min_ratio)
print("Maximum Distance Ratio = ", b_a_distance_max_ratio)
print("Average Distance Ratio = ", b_a_distance_avg_ratio)
print("Sum Distance Ratio = ", b_a_distance_sum_ratio)
print("-----------------------------------------------")
print("Minimum Duration Ratio = ", b_a_duration_min_ratio)
print("Maximum Duration Ratio = ", b_a_duration_max_ratio)
print("Average Duration Ratio = ", b_a_duration_avg_ratio)
print("Sum Duration Ratio = ", b_a_duration_sum_ratio)
print("-----------------------------------------------")
print("Minimum Monthly Trips Ratio = ", b_a_average_monthly_trip)
print("Maximum Monthly Trips Ratio = ", b_a_minimum_monthly_trip)
print("Average Monthly Trips Ratio = ", b_a_maximum_monthly_trip)
print("-----------------------------------------------")
print("Minimum Daily Trips Ratio = ", b_a_average_daily_trip)
print("Maximum Daily Trips Ratio = ", b_a_minimum_daily_trip)
print("Average Daily Trips Ratio = ", b_a_maximum_daily_trip)
print("-----------------------------------------------")
print("Minimum Hourly Trips Ratio = ", b_a_average_hourly_trip)
print("Maximum Hourly Trips Ratio = ", b_a_minimum_hourly_trip)
print("Average Hourly Trips Ratio = ", b_a_maximum_hourly_trip)
Use Ratios of Company B to estimate Scooters in Company B¶
In [72]:
avg_of_minimum = (b_a_distance_min_ratio + b_a_duration_min_ratio + b_a_minimum_monthly_trip + b_a_minimum_daily_trip + b_a_minimum_hourly_trip)/5
avg_of_maximum = (b_a_distance_max_ratio + b_a_duration_max_ratio + b_a_maximum_monthly_trip + b_a_maximum_daily_trip + b_a_maximum_hourly_trip)/5
avg_of_sum = (b_a_distance_sum_ratio + b_a_duration_sum_ratio) /2
avg_of_avg = (b_a_distance_avg_ratio + b_a_duration_avg_ratio + b_a_average_monthly_trip + b_a_average_daily_trip + b_a_average_hourly_trip)/5
avg_of_avg, avg_of_maximum, avg_of_minimum, avg_of_sum
Out[72]:
In [78]:
overall_average = (avg_of_avg*0.3 + avg_of_maximum*0.2 + avg_of_minimum*0.2 + avg_of_sum*0.3)
overall_average
Out[78]:
In [79]:
print("Estimated Number of Trips of Company B = ", round(training_data["TripID"].nunique()*overall_average))
print("Actual Number of Trips of Company B = ", testing_data["trip_id"].nunique())
In [82]:
print("Estimated Average Trip Duration of Company B = ", round(training_data["tripDuration(minutes)"].mean()*overall_average))
print("Actual Average Trip Duration of Company B = ", round(testing_data["tripDuration(minutes)"].mean()))
In [83]:
print("Estimated Average Distance Covered of Company B = ", round(training_data["distance_meters"].mean()*overall_average))
print("Actual Average Distance Covered of Company B = ", round(testing_data["distance_meters"].mean()))
In [77]:
round(training_data["ScooterID"].nunique()*overall_average)
Out[77]:
Validation & Conclusion¶
In [89]:
trip_ratio = testing_data["trip_id"].nunique() / training_data["TripID"].nunique()
print('The Ratio of Trips of Company B to A = ', trip_ratio)
duration_ratio = testing_data["tripDuration(minutes)"].mean() / training_data["tripDuration(minutes)"].mean()
print('The Ratio of Average Duration of Company B to A = ', duration_ratio)
duration_ratio = testing_data["distance_meters"].mean() / training_data["distance_meters"].mean()
print('The Ratio of Average Distance Covered of Company B to A = ', duration_ratio)
The number of trips by Company A's scooters are almost double of company B's scooters, because Company B's scooters usually cover long distance and the trip duration is near to double of Company A. Therefor we can conclude that the number of scooters of company B is same region will less than company A, hence
The Estimated Number of Scooters of Company B are Approx 260¶
Company A¶
Figure 3: (generated using PowerBI) - Left (Starting Points of Journey) - Right (Ending Points of Journey)
Company B¶
Figure 4: (generated using PowerBI) - Left (Starting Points of Journey) - Right (Ending Points of Journey)
Summary to a business stakeholder describing your findings¶
- Trips of Company A are greater than (on hourly, daily, monthly basis) as compared to company B. This is due to fact that the trip duration and distance in company B is greater than A, i-e the scooters of B cover long distance.
- There is proper need of properly resource scheduling, such as in my analysis you can see that some regions are dense (more source and destinations of trips) as compared to other regions, hence the availability should be made sure in those regions. Check in figure A. Red, Green and Blue regions have high density, hence make sure that you have scooters available here to have high return on investment.
- We should not ride scooters randomly in this region/ county/ state, instead define terroritries. For reference check Image B. Then do scheduling according to each territory.
Both of above stratagies will help us to reduce response and turn around time (time required to move from source to destination and then back).
In [ ]: